"""
会话数据访问对象 (Session DAO)
提供会话和消息的数据库操作接口
"""

from typing import List, Optional, Dict, Any
from datetime import datetime
import uuid
import logging

from .supabase_client import get_supabase_client
from pydantic import BaseModel

logger = logging.getLogger(__name__)

# 数据模型
class SessionModel(BaseModel):
    id: str
    user_id: str = '00000000-0000-0000-0000-000000000000'  # 默认用户ID（单用户模式）
    title: str
    role_id: Optional[str] = None
    model: str = 'deepseek-v3-1-250821'
    search_enabled: bool = False
    memo: Optional[str] = None
    memo_enabled: bool = False
    is_favorite: bool = False
    is_archived: bool = False
    created_at: datetime
    updated_at: datetime

class MessageModel(BaseModel):
    id: str
    session_id: str
    role: str  # 'user', 'assistant', 'system'
    content: str
    model: Optional[str] = None
    created_at: datetime
    message_index: int

class SessionDAO:
    """会话数据访问对象"""
    
    def __init__(self):
        self.supabase = get_supabase_client()
    
    async def create_session(self, title: str, role_id: Optional[str] = None, 
                           model: str = 'deepseek-v3-1-250821', 
                           user_id: str = '00000000-0000-0000-0000-000000000000',
                           id: Optional[str] = None, **kwargs) -> SessionModel:
        """创建新会话"""
        try:
            session_data = {
                'title': title,
                'role_id': role_id,
                'model': model,
                'user_id': user_id,
                'search_enabled': kwargs.get('search_enabled', False),
                'memo': kwargs.get('memo'),
                'memo_enabled': kwargs.get('memo_enabled', False),
                'is_favorite': kwargs.get('is_favorite', False),
                'is_archived': kwargs.get('is_archived', False)
            }
            
            if id:
                session_data['id'] = id
            
            result = self.supabase.table('sessions').insert(session_data).execute()
            
            if result.data:
                session_dict = result.data[0]
                return SessionModel(**session_dict)
            else:
                raise Exception("创建会话失败，未返回数据")
                
        except Exception as e:
            logger.error(f"创建会话失败: {e}")
            raise
    
    async def get_session(self, session_id: str) -> Optional[SessionModel]:
        """根据ID获取会话"""
        try:
            result = self.supabase.table('sessions').select('*').eq('id', session_id).execute()
            
            if result.data:
                session_dict = result.data[0]
                return SessionModel(**session_dict)
            return None
            
        except Exception as e:
            logger.error(f"获取会话失败 {session_id}: {e}")
            return None
    
    async def get_sessions(self, page: int = 1, page_size: int = 20, 
                          query: str = "", filter_type: str = "") -> Dict[str, Any]:
        """分页获取会话列表"""
        try:
            # 计算偏移量
            offset = (page - 1) * page_size
            
            # 构建查询
            query_builder = self.supabase.table('sessions').select('*')
            
            # 添加筛选条件
            if filter_type == "favorite":
                query_builder = query_builder.eq('is_favorite', True)
            elif filter_type == "archived":
                query_builder = query_builder.eq('is_archived', True)
            else:
                # 默认只显示未归档的
                query_builder = query_builder.eq('is_archived', False)
            
            # 添加搜索条件 - 智能搜索
            if query.strip():
                # 尝试使用智能搜索（搜索标题和消息内容）
                matched_sessions = await self._search_in_messages(query)
                if matched_sessions:
                    # 如果找到匹配的会话，按匹配的会话ID过滤
                    session_ids = [s for s in matched_sessions]
                    if session_ids:
                        # 使用 IN 查询匹配的会话
                        query_builder = query_builder.in_('id', session_ids)
                else:
                    # 降级到标题搜索
                    query_builder = query_builder.ilike('title', f'%{query}%')
            
            # 排序、分页
            result = query_builder.order('updated_at', desc=True).range(offset, offset + page_size - 1).execute()
            
            # 获取总数
            count_result = self.supabase.table('sessions').select('id', count='exact').execute()
            total_count = count_result.count if count_result.count is not None else 0
            
            sessions = [SessionModel(**session) for session in result.data] if result.data else []
            
            return {
                'sessions': sessions,
                'total': total_count,
                'page': page,
                'pageSize': page_size,
                'totalPages': (total_count + page_size - 1) // page_size
            }
            
        except Exception as e:
            logger.error(f"获取会话列表失败: {e}")
            return {
                'sessions': [],
                'total': 0,
                'page': page,
                'pageSize': page_size,
                'totalPages': 0
            }
    
    async def update_session(self, session_id: str, updates: Dict[str, Any]) -> Optional[SessionModel]:
        """更新会话"""
        try:
            result = self.supabase.table('sessions').update(updates).eq('id', session_id).execute()
            
            if result.data:
                session_dict = result.data[0]
                return SessionModel(**session_dict)
            return None
            
        except Exception as e:
            logger.error(f"更新会话失败 {session_id}: {e}")
            return None
    
    async def delete_session(self, session_id: str) -> bool:
        """删除会话（级联删除消息）"""
        try:
            result = self.supabase.table('sessions').delete().eq('id', session_id).execute()
            return len(result.data) > 0 if result.data else False
            
        except Exception as e:
            logger.error(f"删除会话失败 {session_id}: {e}")
            return False
    
    async def _search_in_messages(self, query: str) -> List[str]:
        """在消息内容中搜索，返回匹配的会话ID列表（优化版本）"""
        try:
            # 优化方案：使用单个查询获取所有匹配的会话ID
            # 使用异步并发查询减少总时间
            
            import asyncio
            
            async def search_titles():
                result = self.supabase.table('sessions').select('id').ilike('title', f'%{query}%').execute()
                return {row['id'] for row in result.data} if result.data else set()
            
            async def search_messages():
                result = self.supabase.table('messages').select('session_id').ilike('content', f'%{query}%').execute()
                return {row['session_id'] for row in result.data} if result.data else set()
            
            async def search_memos():
                result = self.supabase.table('sessions').select('id').ilike('memo', f'%{query}%').execute()
                return {row['id'] for row in result.data} if result.data else set()
            
            # 并发执行所有搜索查询，显著减少总响应时间
            title_ids, message_ids, memo_ids = await asyncio.gather(
                search_titles(),
                search_messages(), 
                search_memos(),
                return_exceptions=True
            )
            
            # 处理可能的异常
            all_session_ids = set()
            if not isinstance(title_ids, Exception):
                all_session_ids |= title_ids
            if not isinstance(message_ids, Exception):
                all_session_ids |= message_ids  
            if not isinstance(memo_ids, Exception):
                all_session_ids |= memo_ids
            
            return list(all_session_ids)
            
        except Exception as e:
            logger.error(f"消息内容搜索失败: {e}")
            return []

class MessageDAO:
    """消息数据访问对象"""
    
    def __init__(self):
        self.supabase = get_supabase_client()
    
    async def add_message(self, session_id: str, role: str, content: str, 
                         model: Optional[str] = None, message_index: Optional[int] = None) -> MessageModel:
        """添加消息到会话"""
        try:
            # 如果没有指定消息索引，则获取下一个消息索引
            if message_index is None:
                result = self.supabase.table('messages').select('message_index').eq('session_id', session_id).order('message_index', desc=True).limit(1).execute()
                
                next_index = 1
                if result.data:
                    next_index = result.data[0]['message_index'] + 1
            else:
                next_index = message_index
            
            message_data = {
                'session_id': session_id,
                'role': role,
                'content': content,
                'model': model,
                'message_index': next_index
            }
            
            result = self.supabase.table('messages').insert(message_data).execute()
            
            if result.data:
                message_dict = result.data[0]
                return MessageModel(**message_dict)
            else:
                raise Exception("添加消息失败，未返回数据")
                
        except Exception as e:
            logger.error(f"添加消息失败: {e}")
            raise
    
    async def get_messages(self, session_id: str) -> List[MessageModel]:
        """获取会话的所有消息"""
        try:
            result = self.supabase.table('messages').select('*').eq('session_id', session_id).order('message_index').execute()
            
            return [MessageModel(**msg) for msg in result.data] if result.data else []
            
        except Exception as e:
            logger.error(f"获取消息失败 {session_id}: {e}")
            return []
    
    async def update_message(self, message_id: str, content: str) -> Optional[MessageModel]:
        """更新消息内容"""
        try:
            result = self.supabase.table('messages').update({'content': content}).eq('id', message_id).execute()
            
            if result.data:
                message_dict = result.data[0]
                return MessageModel(**message_dict)
            return None
            
        except Exception as e:
            logger.error(f"更新消息失败 {message_id}: {e}")
            return None
    
    async def delete_messages_after(self, session_id: str, message_index: int) -> bool:
        """删除指定索引之后的所有消息（用于编辑功能）"""
        try:
            result = self.supabase.table('messages').delete().eq('session_id', session_id).gt('message_index', message_index).execute()
            return True
            
        except Exception as e:
            logger.error(f"删除后续消息失败 {session_id}, index {message_index}: {e}")
            return False
    
    async def get_recent_messages(self, session_id: str, limit: int = 10) -> List[MessageModel]:
        """获取最近的N条消息（用于上下文管理）"""
        try:
            result = self.supabase.table('messages').select('*').eq('session_id', session_id).order('message_index', desc=True).limit(limit).execute()
            
            messages = [MessageModel(**msg) for msg in result.data] if result.data else []
            # 按正序返回
            return list(reversed(messages))
            
        except Exception as e:
            logger.error(f"获取最近消息失败 {session_id}: {e}")
            return []